/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.comut.crud.dao;

import com.comut.crud.pojo.mobile.Mobile;
import com.comut.crud.pojo.mobile.MobileUpdate;
import com.comut.lib.error.ErrorCodes;
import com.comut.lib.exception.BaseException;
import com.comut.lib.exception.ObjectFoundException;
import com.comut.lib.exception.ObjectNotFoundException;
import java.sql.*;
import java.util.ResourceBundle;
import org.apache.commons.lang.StringEscapeUtils;
import org.apache.log4j.Logger;

/**
 *
 * @author asantos
 */
public class MobileDao  {
    

    private static Logger _logger = Logger.getLogger(MobileDao.class.getName());
    private static final int _appMoovefleetId = Integer.parseInt(ResourceBundle.getBundle("application").getString("appMoovefleetId"));
    private static final int _appIfleetId = Integer.parseInt(ResourceBundle.getBundle("application").getString("appIfleetId"));
    private Connection _moovefleetConnection;
    private Connection _ifleetConnection;

    
    public MobileDao(Connection moovefleetConnection, Connection ifleetConnection) {
        this._moovefleetConnection = moovefleetConnection;
        this._ifleetConnection = ifleetConnection;
    }

    public ResultSet getMobile(int uid) {

        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_getMobile](?)}";
        try {
            stmt = _moovefleetConnection.prepareCall(query);
            stmt.setInt(1, uid);
            rs = stmt.executeQuery();
        } catch (SQLException ex) {
            _logger.error("getMobile error");
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
        }

        return rs;
    }

    public int createMobile(Mobile mobile) {     
        int app = getAppWithMaxId();
        int uid = -1;
        
        //insert into auth.permission
        if(app == _appMoovefleetId){
             uid = insertIntoAuthPermission(_moovefleetConnection, uid, mobile.getId_fleet(), mobile.getId_mobile());
             insertIntoAuthPermission(_ifleetConnection, uid, mobile.getId_fleet(), mobile.getId_mobile());
        }
        else{
             uid = insertIntoAuthPermission(_ifleetConnection, uid, mobile.getId_fleet(), mobile.getId_mobile());
             insertIntoAuthPermission(_moovefleetConnection, uid, mobile.getId_fleet(), mobile.getId_mobile());
        }
        
        _logger.info("insertIntoAuthPermission ok");
        
        //insert mobile
        /* @uid int,
	@id_mobile int,
	@id_fleet int,
	@alias varchar(12),
	@id_image int,
	@serial_number varchar(25),
	@device_type int,
	@gsmNumber varchar(25),
	@imei varchar(25),
	@license_plate varchar(10),
	@iccid varchar(12),
	@vehicle_type int,
	@brand varchar(20),
	@model varchar(20),
	@owner varchar(20),
	@fuel_error_coeficient float,
	@equipment_type smallint        
        */
        if(mobile.getId_image()==null){
            mobile.setId_image("140");
        }
        
        if(mobile.getFuel_error_coefficient()==null){
            mobile.setFuel_error_coefficient("1");
        }
        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_createMobile](?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
        try {
            stmt = _moovefleetConnection.prepareCall(query);
            stmt.setInt(1,uid);
            stmt.setInt(2,mobile.getId_mobile());
            stmt.setInt(3,mobile.getId_fleet());
            stmt.setString(4,mobile.getAlias());
            stmt.setString(5,mobile.getId_image());
            stmt.setString(6,mobile.getSerial_number());
            stmt.setString(7,mobile.getGsm_number());
            stmt.setString(8,mobile.getImei());
            stmt.setString(9,mobile.getLicense_plate());
            stmt.setString(10,mobile.getIccid());
            stmt.setString(11,mobile.getVehicle_type());
            
            String brand = mobile.getBrand();
            if(brand != null){
                brand = StringEscapeUtils.escapeSql(brand);
            }
            stmt.setString(12,brand);
            
            String model = mobile.getModel();
            if(model != null){
                model = StringEscapeUtils.escapeSql(model);
            }            
            stmt.setString(13,model);            
            
            String owner = mobile.getOwner();
            if(owner != null){
                owner = StringEscapeUtils.escapeSql(owner);
            }            
            stmt.setString(14,owner);
            
            stmt.setString(15,mobile.getFuel_error_coefficient());
            stmt.setString(16,mobile.getEquipment_type());
            stmt.execute();
        
        } catch (Exception ex) {
            _logger.error("create mobile mooveFleet error", ex);
            throw new BaseException(ex, ErrorCodes.GENERAL_ERROR);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
        
        _logger.info("insertIntoMooveFleet ok");
        
        //insert into ifleet
        try {
            stmt = _ifleetConnection.prepareCall(query);
            stmt.setInt(1,uid);
            stmt.setInt(2,mobile.getId_mobile());
            stmt.setInt(3,mobile.getId_fleet());
            stmt.setString(4,mobile.getAlias());
            stmt.setString(5,mobile.getId_image());
            stmt.setString(6,mobile.getSerial_number());
            stmt.setString(7,mobile.getGsm_number());
            stmt.setString(8,mobile.getImei());
            stmt.setString(9,mobile.getLicense_plate());
            stmt.setString(10,mobile.getIccid());
            stmt.setString(11,mobile.getVehicle_type());
            stmt.setString(12,mobile.getBrand());
            stmt.setString(13,mobile.getModel());
            stmt.setString(14,mobile.getOwner());
            stmt.setString(15,mobile.getFuel_error_coefficient());
            stmt.setString(16,mobile.getEquipment_type());

            stmt.execute();

        
        } catch (Exception ex) {
            _logger.error("create mobile iFleet error", ex);
            throw new BaseException(ex, ErrorCodes.GENERAL_ERROR);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
        
        _logger.info("insertIntoIFleet ok");

        return uid;
        
    }
    
    /**
     * function to insert into auth.permission
     * if the uid is equal to -1 then the sqlserver will generate a new id
     * else the uid will be the id of the table
     * @param connection
     * @param uid
     * @param id_fleet
     * @param id_mobile
     * @return 
     */
    private int insertIntoAuthPermission(Connection connection, int uid, int id_fleet, int id_mobile){
        int toRet = -1;
        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_insertIntoAuthPermision](?,?)}";
        try {

            stmt = connection.prepareCall(query);
            stmt.setString(1, id_fleet+", "+id_mobile);
            if(uid==-1)
                stmt.setString(2, null);
            else
                stmt.setInt(2, uid);
            rs = stmt.executeQuery();

            if (rs.next()) {
                toRet = rs.getInt(1);
            }
            
            
        } catch (SQLException ex) {
            _logger.error("insertIntoAuthPermission error", ex);
            if (ex.getErrorCode() == ErrorCodes.OBJECT_FOUND) {
                throw new ObjectFoundException(ex, ex.getErrorCode());
            } else {
                throw new BaseException(ex, ex.getErrorCode());
            }
        } catch (Exception ex) {
            _logger.error("createPoi error", ex);
            throw new BaseException(ex, ErrorCodes.GENERAL_ERROR);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }

        return toRet;
        
    }
    

    /**
     * Update the mobile table in SQLServer
     * Also create or update [mobile_group]
     * @param mobile Object with the where clause and the update clause
     */
    public void updateMobile(MobileUpdate mobile) {

        if (mobile.isToUpdateConexaMobile()) {

            String update = "UPDATE [conexa_europa_q108].[dbo].[mobile] SET ";
            boolean isNotFirst = true;
            if(mobile.isId_imageFlag()){
                isNotFirst = false;
                update +=" [icon_pos]="+mobile.getId_image();
            }
            
            if(mobile.isAliasFlag()){
                if(!isNotFirst)
                    update +=" ,";
                
                update +=" [alias]='"+StringEscapeUtils.escapeSql(mobile.getAlias())+"'";
            }
            
            String where = " WHERE [id_mobile]= "+mobile.getId_mobile()+" AND [id_fleet]=" + mobile.getId_fleet();

            PreparedStatement stmt = null;
            try {
                stmt = _moovefleetConnection.prepareStatement(update + where);
                int updateRows = stmt.executeUpdate();
                if (updateRows == 0) {
                    throw new ObjectNotFoundException("The mobile does not exist in mooveFleet(Conexa.Mobile): "+update + where, ErrorCodes.OBJECT_NOT_FOUND);
                }
            } catch (SQLException ex) {
                _logger.fatal("updateMobile mooveFleet(Conexa.Mobile) error: "+update + where, ex);
                throw new BaseException(ex, ex.getErrorCode());
            } finally {
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException ignore) {
                    }
                }
            }
            
            stmt = null;
            try {
                stmt = _ifleetConnection.prepareStatement(update + where);
                int updateRows = stmt.executeUpdate();
                if (updateRows == 0) {
                    throw new ObjectNotFoundException("The mobile does not exist in iFleet(Conexa.Mobile): "+update + where, ErrorCodes.OBJECT_NOT_FOUND);
                }
            } catch (SQLException ex) {
                _logger.fatal("updateMobile iFleet(Conexa.Mobile) error: "+update + where, ex);
                throw new BaseException(ex, ex.getErrorCode());
            } finally {
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException ignore) {
                    }
                }
            }

        }
        
        
        if (mobile.isToUpdateConexaConf()) {

            String update = "UPDATE [conexa_europa_q108].[dbo].[conf] SET ";            
            if(mobile.isSerial_numberFlag()){
                update +=" [serial]='"+StringEscapeUtils.escapeSql(mobile.getSerial_number())+"'";
            }
            
            
            String where = " WHERE [id_mobile]= "+mobile.getId_mobile()+" AND [id_fleet]=" + mobile.getId_fleet();

            PreparedStatement stmt = null;
            try {
                stmt = _moovefleetConnection.prepareStatement(update + where);
                int updateRows = stmt.executeUpdate();
                if (updateRows == 0) {
                    throw new ObjectNotFoundException("The mobile does not exist in mooveFleet(Conexa.Conf): "+update + where, ErrorCodes.OBJECT_NOT_FOUND);
                }
            } catch (SQLException ex) {
                _logger.fatal("updateMobile mooveFleet(Conexa.Conf) error: "+update + where, ex);
                throw new BaseException(ex, ex.getErrorCode());
            } finally {
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException ignore) {
                    }
                }
            }
            
            stmt = null;
            try {
                stmt = _ifleetConnection.prepareStatement(update + where);
                int updateRows = stmt.executeUpdate();
                if (updateRows == 0) {
                    throw new ObjectNotFoundException("The mobile does not exist in iFleet(Conexa.Conf): "+update + where, ErrorCodes.OBJECT_NOT_FOUND);
                }
            } catch (SQLException ex) {
                _logger.fatal("updateMobile iFleet(Conexa.Conf) error: "+update + where, ex);
                throw new BaseException(ex, ex.getErrorCode());
            } finally {
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException ignore) {
                    }
                }
            }

        }
        
        
        
        if (mobile.isToUpdateConexaDevice()) {
            
            if (mobile.isGsm_numberFlag()){
                
                String update = "UPDATE [conexa_europa_q108].[dbo].[device] SET ";
                update += " [address]='" + StringEscapeUtils.escapeSql(mobile.getGsm_number())+"'";

                String where = " WHERE [id_device] = 2 AND [type] = 2 AND [id_mobile]= " + mobile.getId_mobile() + " AND [id_fleet]=" + mobile.getId_fleet();

                PreparedStatement stmt = null;
                try {
                    stmt = _moovefleetConnection.prepareStatement(update + where);
                    int updateRows = stmt.executeUpdate();
                    if (updateRows == 0) {
                        throw new ObjectNotFoundException("The mobile does not exist in mooveFleet(Conexa.device) gsm: "+update + where, ErrorCodes.OBJECT_NOT_FOUND);
                    }
                } catch (SQLException ex) {
                    _logger.fatal("updateMobile mooveFleet(Conexa.device) gsm error: "+update + where, ex);
                    throw new BaseException(ex, ex.getErrorCode());
                } finally {
                    if (stmt != null) {
                        try {
                            stmt.close();
                        } catch (SQLException ignore) {
                        }
                    }
                }

                stmt = null;
                try {
                    stmt = _ifleetConnection.prepareStatement(update + where);
                    int updateRows = stmt.executeUpdate();
                    if (updateRows == 0) {
                        throw new ObjectNotFoundException("The mobile does not exist in iFleet(Conexa.device) gsm: "+update + where, ErrorCodes.OBJECT_NOT_FOUND);
                    }
                } catch (SQLException ex) {
                    _logger.fatal("updateMobile iFleet(Conexa.device) gsm error: "+update + where, ex);
                    throw new BaseException(ex, ex.getErrorCode());
                } finally {
                    if (stmt != null) {
                        try {
                            stmt.close();
                        } catch (SQLException ignore) {
                        }
                    }
                }

            }
            
            if (mobile.isImeiFlag()){
                
                String update = "UPDATE [conexa_europa_q108].[dbo].[device] SET ";
                update += " [address]='"+ StringEscapeUtils.escapeSql(mobile.getImei())+"'";

                String where = " WHERE [id_device]=1 AND [type]=7 AND [id_mobile]= " + mobile.getId_mobile() + " AND [id_fleet]=" + mobile.getId_fleet();

                PreparedStatement stmt = null;
                try {
                    stmt = _moovefleetConnection.prepareStatement(update + where);
                    int updateRows = stmt.executeUpdate();
                    if (updateRows == 0) {
                        throw new ObjectNotFoundException("The mobile does not exist in mooveFleet(Conexa.device) imei: "+update + where, ErrorCodes.OBJECT_NOT_FOUND);
                    }
                } catch (SQLException ex) {
                    _logger.fatal("updateMobile mooveFleet(Conexa.device) imei error: "+update + where, ex);
                    throw new BaseException(ex, ex.getErrorCode());
                } finally {
                    if (stmt != null) {
                        try {
                            stmt.close();
                        } catch (SQLException ignore) {
                        }
                    }
                }

                stmt = null;
                try {
                    stmt = _ifleetConnection.prepareStatement(update + where);
                    int updateRows = stmt.executeUpdate();
                    if (updateRows == 0) {
                        throw new ObjectNotFoundException("The mobile does not exist in iFleet(Conexa.device) imei: "+update + where, ErrorCodes.OBJECT_NOT_FOUND);
                    }
                } catch (SQLException ex) {
                    _logger.fatal("updateMobile iFleet(Conexa.device) imei error: "+update + where, ex);
                    throw new BaseException(ex, ex.getErrorCode());
                } finally {
                    if (stmt != null) {
                        try {
                            stmt.close();
                        } catch (SQLException ignore) {
                        }
                    }
                }

            }

        }
        
        if (mobile.isToUpdateConexaPhpMobileInfo()) {

            String update = "UPDATE [conexa_europa_q108].[dbo].[php_mobile_info] SET ";
            boolean isNotFirst = true;
            
            if(mobile.isLicense_plateFlag()){
                isNotFirst = false;
                update +=" [license_plate]='"+ StringEscapeUtils.escapeSql(mobile.getLicense_plate())+"'";
            }
            
            if(mobile.isIccidFlag()){
                if(!isNotFirst)
                    update +=" ,";
                
                update +=" [iccid]='"+StringEscapeUtils.escapeSql(mobile.getIccid())+"'";
                isNotFirst = false;
            }
            
            if(mobile.isVehicle_typeFlag()){
                if(!isNotFirst)
                    update +=" ,";
                
                String vehicle_type = mobile.getVehicle_type();
                if(vehicle_type != null)
                    vehicle_type = "'"+vehicle_type+"'";
                
                update +=" [vehicle_type]="+vehicle_type;
                isNotFirst = false;
            }
            
            if(mobile.isBrandFlag()){
                if(!isNotFirst)
                    update +=" ,";
                
                String brand = mobile.getBrand();
                if(brand != null)
                    brand = "'"+StringEscapeUtils.escapeSql(brand)+"'";
                
                update +=" [marca]="+brand;
                isNotFirst = false;
            }
            
            if(mobile.isModelFlag()){
                if(!isNotFirst)
                    update +=" ,";
                
                String model = mobile.getModel();
                if(model != null)
                    model = "'"+StringEscapeUtils.escapeSql(model)+"'";
                
                update +=" [modelo]="+model;
                isNotFirst = false;
            }
            
            if(mobile.isOwnerFlag()){
                if(!isNotFirst)
                    update +=" ,";
                
                String owner = mobile.getOwner();
                if(owner != null)
                    owner = "'"+StringEscapeUtils.escapeSql(owner)+"'";
                
                update +=" [owner]="+owner;
                isNotFirst = false;
            }
            
            
            
            if(mobile.isFuel_error_coefficientFlag()){
                if(!isNotFirst)
                    update +=" ,";
                
                update +=" [fuel_error_coeficient]="+mobile.getFuel_error_coefficient();            
            }
            
            String where = " WHERE [id_mobile]= "+mobile.getId_mobile()+" AND [id_fleet]=" + mobile.getId_fleet();

            PreparedStatement stmt = null;
            try {
                stmt = _moovefleetConnection.prepareStatement(update + where);
                int updateRows = stmt.executeUpdate();
                if (updateRows == 0) {
                    throw new ObjectNotFoundException("The mobile does not exist in mooveFleet(Conexa.php_mobile_info): "+update + where, ErrorCodes.OBJECT_NOT_FOUND);
                }
            } catch (SQLException ex) {
                _logger.fatal("updateMobile mooveFleet(Conexa.php_mobile_info) error: "+update + where, ex);
                throw new BaseException(ex, ex.getErrorCode());
            } finally {
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException ignore) {
                    }
                }
            }
            
            stmt = null;
            try {
                stmt = _ifleetConnection.prepareStatement(update + where);
                int updateRows = stmt.executeUpdate();
                if (updateRows == 0) {
                    throw new ObjectNotFoundException("The mobile does not exist in iFleet(Conexa.php_mobile_info): "+update + where, ErrorCodes.OBJECT_NOT_FOUND);
                }
            } catch (SQLException ex) {
                _logger.fatal("updateMobile iFleet(Conexa.php_mobile_info) error: "+update + where, ex);
                throw new BaseException(ex, ex.getErrorCode());
            } finally {
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException ignore) {
                    }
                }
            }

        }
        
        if (mobile.isToUpdateComutDevMobile()){

            String update = "UPDATE [comut_dev_v1].[dbo].[mobile] SET ";            
            if(mobile.isAliasFlag()){
                update +=" [alias]='"+StringEscapeUtils.escapeSql(mobile.getAlias())+"'";
            }
            
            
            String where = " WHERE [id_mobile]= "+mobile.getId_mobile()+" AND [id_fleet]=" + mobile.getId_fleet();

            PreparedStatement stmt = null;
            try {
                stmt = _moovefleetConnection.prepareStatement(update + where);
                int updateRows = stmt.executeUpdate();
                if (updateRows == 0) {
                    throw new ObjectNotFoundException("The mobile does not exist in mooveFleet(comut_dev_v1.mobile): "+update + where, ErrorCodes.OBJECT_NOT_FOUND);
                }
            } catch (SQLException ex) {
                _logger.fatal("updateMobile mooveFleet(comut_dev_v1.mobile) error: "+update + where, ex);
                throw new BaseException(ex, ex.getErrorCode());
            } finally {
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException ignore) {
                    }
                }
            }
            
            stmt = null;
            try {
                stmt = _ifleetConnection.prepareStatement(update + where);
                int updateRows = stmt.executeUpdate();
                if (updateRows == 0) {
                    throw new ObjectNotFoundException("The mobile does not exist in iFleet(comut_dev_v1.mobile): "+update + where, ErrorCodes.OBJECT_NOT_FOUND);
                }
            } catch (SQLException ex) {
                _logger.fatal("updateMobile iFleet(comut_dev_v1.mobile) error: "+update + where, ex);
                throw new BaseException(ex, ex.getErrorCode());
            } finally {
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException ignore) {
                    }
                }
            }

        }
        
        
        
        
        if (mobile.isToUpdateComutDevDevice()) {
            
            if (mobile.isGsm_numberFlag()){
                
                String update = "UPDATE [comut_dev_v1].[dbo].[device] SET ";
                update += " [address]='"+StringEscapeUtils.escapeSql(mobile.getGsm_number())+"'";

                String where = " WHERE [id_type] = 14 AND [id_mobile]= " + mobile.getId_mobile() + " AND [id_fleet]=" + mobile.getId_fleet();

                PreparedStatement stmt = null;
                try {
                    stmt = _moovefleetConnection.prepareStatement(update + where);
                    int updateRows = stmt.executeUpdate();
                    if (updateRows == 0) {
                        throw new ObjectNotFoundException("The mobile does not exist in mooveFleet(comut_dev_v1.device) gsm: "+update + where, ErrorCodes.OBJECT_NOT_FOUND);
                    }
                } catch (SQLException ex) {
                    _logger.fatal("updateMobile mooveFleet(comut_dev_v1.device) gsm error: "+update + where, ex);
                    throw new BaseException(ex, ex.getErrorCode());
                } finally {
                    if (stmt != null) {
                        try {
                            stmt.close();
                        } catch (SQLException ignore) {
                        }
                    }
                }

                stmt = null;
                try {
                    stmt = _ifleetConnection.prepareStatement(update + where);
                    int updateRows = stmt.executeUpdate();
                    if (updateRows == 0) {
                        throw new ObjectNotFoundException("The mobile does not exist in iFleet(comut_dev_v1.device) gsm: "+update + where, ErrorCodes.OBJECT_NOT_FOUND);
                    }
                } catch (SQLException ex) {
                    _logger.fatal("updateMobile iFleet(comut_dev_v1.device) gsm error: "+update + where, ex);
                    throw new BaseException(ex, ex.getErrorCode());
                } finally {
                    if (stmt != null) {
                        try {
                            stmt.close();
                        } catch (SQLException ignore) {
                        }
                    }
                }

            }
            
            if (mobile.isImeiFlag()){
                
                String update = "UPDATE [comut_dev_v1].[dbo].[device] SET ";
                update += " [address]='"+StringEscapeUtils.escapeSql(mobile.getImei())+"'";

                String where = " WHERE [id_type]=10 AND [id_mobile]= " + mobile.getId_mobile() + " AND [id_fleet]=" + mobile.getId_fleet();

                PreparedStatement stmt = null;
                try {
                    stmt = _moovefleetConnection.prepareStatement(update + where);
                    int updateRows = stmt.executeUpdate();
                    if (updateRows == 0) {
                        throw new ObjectNotFoundException("The mobile does not exist in mooveFleet(comut_dev_v1.device) imei: "+update + where, ErrorCodes.OBJECT_NOT_FOUND);
                    }
                } catch (SQLException ex) {
                    _logger.fatal("updateMobile mooveFleet(Conexa.device) imei error: "+update + where, ex);
                    throw new BaseException(ex, ex.getErrorCode());
                } finally {
                    if (stmt != null) {
                        try {
                            stmt.close();
                        } catch (SQLException ignore) {
                        }
                    }
                }

                stmt = null;
                try {
                    stmt = _ifleetConnection.prepareStatement(update + where);
                    int updateRows = stmt.executeUpdate();
                    if (updateRows == 0) {
                        throw new ObjectNotFoundException("The mobile does not exist in iFleet(comut_dev_v1.device) imei: "+update + where, ErrorCodes.OBJECT_NOT_FOUND);
                    }
                } catch (SQLException ex) {
                    _logger.fatal("updateMobile iFleet(Conexa.device) imei error: "+update + where, ex);
                    throw new BaseException(ex, ex.getErrorCode());
                } finally {
                    if (stmt != null) {
                        try {
                            stmt.close();
                        } catch (SQLException ignore) {
                        }
                    }
                }

            }

        }
        
        if (mobile.isToUpdateComutDevConf()) {

            String update = "UPDATE [comut_dev_v1].[dbo].[conf] SET ";
            boolean isNotFirst = true;
            
            if(mobile.isEquipment_typeFlag()){
                isNotFirst = false;
                update +=" [id_mobile_type]="+mobile.getEquipment_type();
            }
            
            if(mobile.isSerial_numberFlag()){
                if(!isNotFirst)
                    update +=" ,";
                
                update +=" [serial]='"+StringEscapeUtils.escapeSql(mobile.getSerial_number())+"'";
                isNotFirst = false;
            }
            
            if(mobile.isLicense_plateFlag()){
                if(!isNotFirst)
                    update +=" ,";
                
                update +=" [license]='"+StringEscapeUtils.escapeSql(mobile.getLicense_plate())+"'";
                isNotFirst = false;
            }
            
            if(mobile.isBrandFlag()){
                if(!isNotFirst)
                    update +=" ,";
                
                String brand = mobile.getBrand();
                if(brand != null)
                    brand = "'"+StringEscapeUtils.escapeSql(brand)+"'";
                
                update +=" [brand]="+brand;
                isNotFirst = false;
            }
            
            if(mobile.isModelFlag()){
                if(!isNotFirst)
                    update +=" ,";
                
                String model = mobile.getModel();
                if(model != null)
                    model = "'"+StringEscapeUtils.escapeSql(model)+"'";
                
                update +=" [model]="+model;
                isNotFirst = false;
            }
            
            if(mobile.isFuel_error_coefficientFlag()){
                if(!isNotFirst)
                    update +=" ,";
                
                update +=" [fuel_error_coeficient]="+mobile.getFuel_error_coefficient();   
            }
            
            String where = " WHERE [id_mobile]= "+mobile.getId_mobile()+" AND [id_fleet]=" + mobile.getId_fleet();

            PreparedStatement stmt = null;
            try {
                stmt = _moovefleetConnection.prepareStatement(update + where);
                int updateRows = stmt.executeUpdate();
                if (updateRows == 0) {
                    throw new ObjectNotFoundException("The mobile does not exist in mooveFleet(comut_dev_v1.conf): "+update + where, ErrorCodes.OBJECT_NOT_FOUND);
                }
            } catch (SQLException ex) {
                _logger.fatal("updateMobile mooveFleet(comut_dev_v1.conf) error: "+update + where, ex);
                throw new BaseException(ex, ex.getErrorCode());
            } finally {
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException ignore) {
                    }
                }
            }
            
            stmt = null;
            try {
                stmt = _ifleetConnection.prepareStatement(update + where);
                int updateRows = stmt.executeUpdate();
                if (updateRows == 0) {
                    throw new ObjectNotFoundException("The mobile does not exist in iFleet(comut_dev_v1.conf): "+update + where, ErrorCodes.OBJECT_NOT_FOUND);
                }
            } catch (SQLException ex) {
                _logger.fatal("updateMobile iFleet(comut_dev_v1.conf) error: "+update + where, ex);
                throw new BaseException(ex, ex.getErrorCode());
            } finally {
                if (stmt != null) {
                    try {
                        stmt.close();
                    } catch (SQLException ignore) {
                    }
                }
            }

        }
    }

    /**
     * delete mobile with the uid,
     * delete from [dbo].[mobile] and [auth].[permission] of moovefleet and ifleet
     * @param id 
     */
    public void deleteMobile(int id) {
        //check if exists historic associated
        checkIfExistsHistoric(id);
        
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_deleteMobile](?)}";
        try {
            stmt = _moovefleetConnection.prepareCall(query);
            stmt.setInt(1, id);
            stmt.execute();
        } catch (SQLException ex) {
            _logger.error("deleteMobile in moovefleet error", ex);
            if (ex.getErrorCode() == ErrorCodes.OBJECT_NOT_FOUND) {
                throw new ObjectFoundException(ex, ex.getErrorCode());
            } else {
                throw new BaseException(ex, ex.getErrorCode());
            }
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
        
        try {
            stmt = _ifleetConnection.prepareCall(query);
            stmt.setInt(1, id);
            stmt.execute();
        } catch (SQLException ex) {
            _logger.error("deleteMobile in ifleet error", ex);
            if (ex.getErrorCode() == ErrorCodes.OBJECT_NOT_FOUND) {
                throw new ObjectFoundException(ex, ex.getErrorCode());
            } else {
                throw new BaseException(ex, ex.getErrorCode());
            }
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }
    
    private void checkIfExistsHistoric(int uid){
        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_uidWithHistoric](?)}";
        try {
            stmt = _moovefleetConnection.prepareCall(query);
            stmt.setInt(1, uid);
                    
            rs = stmt.executeQuery();
            if (rs.next()) {
                throw new BaseException("The UID contains historic associated ", ErrorCodes.MOBILE_WITH_HISTORIC);
            }
        } catch (SQLException ex) {
            _logger.error("checkIfExistsHistoric error");
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }
    
    /**
     * 
     * @return 0 if moovefleet and 1 with ifleet
     */
    private int getAppWithMaxId(){
        int toRet = _appMoovefleetId;
        int mooveFleetRes=-1;
        int iFleetRes=-1;
        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_getMaxUid]()}";
        
        //ask to moovefleet
        try {
            stmt = _moovefleetConnection.prepareCall(query);
                    
            rs = stmt.executeQuery();
            if (rs.next()) {
                mooveFleetRes = rs.getInt(1);
            }
        } catch (SQLException ex) {
            _logger.error("getAppWithMaxId error");
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
        
        //ask to ifleet
        try {
            stmt = _ifleetConnection.prepareCall(query);
                    
            rs = stmt.executeQuery();
            if (rs.next()) {
                iFleetRes = rs.getInt(1);
            }
        } catch (SQLException ex) {
            _logger.error("getAppWithMaxId error");
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
        
        if(iFleetRes > mooveFleetRes)
            toRet = _appIfleetId;
        
        return toRet;
        
    }
    
    /**
     * check if there is other mobile with the pair id_fleet<->id_mobile,
     * if exists throw an exception
     * @param id_fleet
     * @param id_mobile 
     */
    private void checkUniqueIdFleetIdMobile(int id_fleet, int id_mobile) {
        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_getUniqueIdFleetIdMobile](?,?)}";
        try {
            stmt = _moovefleetConnection.prepareCall(query);
            stmt.setInt(1, id_fleet);
            stmt.setInt(2, id_mobile);
                    
            rs = stmt.executeQuery();
            if (rs.next()) {
                throw new ObjectFoundException("The pair id_fleet<->id_mobile already exists", ErrorCodes.IDMOBILE_IDFLEET_FOUND);
            }
        } catch (SQLException ex) {
            _logger.error("checkUniqueIdFleetIdMobile error");
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }

    }
    
    
    /**
     * check if there is other mobile with the license plate,
     * if exists throw an exception
     * @param id_fleet
     * @param id_mobile 
     * @param licensePlate 
     */
    public void checkUniqueLicensePlate(int id_fleet, int id_mobile, String licensePlate) {
        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_getUniqueLicensePlate](?,?,?)}";
        try {
            stmt = _moovefleetConnection.prepareCall(query);
            stmt.setInt(1, id_fleet);
            stmt.setInt(2, id_mobile);
            stmt.setString(3, licensePlate);
                    
            rs = stmt.executeQuery();
            
            if (rs.next()) {
                throw new ObjectFoundException("The license Plate already exists", ErrorCodes.LICENSEPLATE_FOUND);
            }
            
        } catch (SQLException ex) {
            _logger.error("checkUniqueLicensePlate error");
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }
    
    
    /**
     * check if there is other mobile with the Serial Number,
     * if exists throw an exception
     * @param id_fleet
     * @param id_mobile 
     * @param licensePlate 
     */
    public void checkUniqueSerialNumber(int id_fleet, int id_mobile, String serialNumber) {
        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_getUniqueSerialNumber](?,?,?)}";
        try {
            stmt = _moovefleetConnection.prepareCall(query);
            stmt.setInt(1, id_fleet);
            stmt.setInt(2, id_mobile);
            stmt.setString(3, serialNumber);
                    
            rs = stmt.executeQuery();
            
            if (rs.next()) {
                throw new ObjectFoundException("The Serial Number already exists", ErrorCodes.SERIALNUMBER_FOUND);
            }
            
        } catch (SQLException ex) {
            _logger.error("checkUniqueSerialNumber error");
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }
    
    
    
    
    /**
     * check if there is other mobile with the imei,
     * if exists throw an exception
     * @param id_fleet
     * @param id_mobile 
     * @param imei 
     */
    public void checkUniqueImei(int id_fleet, int id_mobile, String imei) {
        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_getUniqueImei](?,?,?)}";
        try {
            stmt = _moovefleetConnection.prepareCall(query);
            stmt.setInt(1, id_fleet);
            stmt.setInt(2, id_mobile);
            stmt.setString(3, imei);
                    
            rs = stmt.executeQuery();
            
            if (rs.next()) {
                throw new ObjectFoundException("The imei already exists", ErrorCodes.IMEI_FOUND);
            }
            
        } catch (SQLException ex) {
            _logger.error("checkUniqueImei error");
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }
    
    
    /**
     * check if there is other mobile with the GsmNumber,
     * if exists throw an exception
     * @param id_fleet
     * @param id_mobile 
     * @param imei 
     */
    public void checkUniqueGsmNumber(int id_fleet, int id_mobile, String gsmNumber) {
        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_getUniqueGsmNumber](?,?,?)}";
        try {
            stmt = _moovefleetConnection.prepareCall(query);
            stmt.setInt(1, id_fleet);
            stmt.setInt(2, id_mobile);
            stmt.setString(3, gsmNumber);
                    
            rs = stmt.executeQuery();
            
            if (rs.next()) {
                throw new ObjectFoundException("The Gsm Number already exists", ErrorCodes.GSMNUMBER_FOUND);
            }
            
        } catch (SQLException ex) {
            _logger.error("checkUniqueGsmNumber error");
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }
    
    
    /**
     * check if there is other mobile with the ICCID,
     * if exists throw an exception
     * @param id_fleet
     * @param id_mobile 
     * @param iccid 
     */
    public void checkUniqueICCID(int id_fleet, int id_mobile, String iccid) {
        ResultSet rs = null;
        CallableStatement stmt = null;
        String query = "{call [crud].[usp_getUniqueICCID](?,?,?)}";
        try {
            stmt = _moovefleetConnection.prepareCall(query);
            stmt.setInt(1, id_fleet);
            stmt.setInt(2, id_mobile);
            stmt.setString(3, iccid);
                    
            rs = stmt.executeQuery();
            
            if (rs.next()) {
                throw new ObjectFoundException("The ICCID already exists", ErrorCodes.ICCID_FOUND);
            }
            
        } catch (SQLException ex) {
            _logger.error("checkUniqueICCID error");
            throw new BaseException(ex, ex.getErrorCode());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignore) {
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ignore) {
                }
            }
        }
    }
    
    
    public void checkUniquenessOnCreate(int id_fleet, int id_mobile, String licensePlate, String serialNumber, String imei, String gsmNumber, String iccid){
        checkUniqueIdFleetIdMobile(id_fleet, id_mobile);
        checkUniqueLicensePlate(id_fleet, id_mobile, licensePlate);
        checkUniqueSerialNumber(id_fleet, id_mobile, serialNumber);
        checkUniqueImei(id_fleet, id_mobile, imei);
        checkUniqueGsmNumber(id_fleet, id_mobile, gsmNumber);
        checkUniqueICCID(id_fleet, id_mobile, iccid);
    }
    
}
